package mypackage.dao.impl;

import mypackage.bean.Profits;
import mypackage.dao.ProfitsDao;
import mypackage.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @author YH925
 * @create 2021/12/12 21:30
 */
public class ProfitsDaoImpl implements ProfitsDao {
    Connection connection;

    {
        try {
            connection = JDBCUtils.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    QueryRunner runner = new QueryRunner();

    /**
     * 每销售一件商品就向销售表中插入一条数据
     * @param goodsId
     * @param saleMoney
     * @param cost
     * @param profit
     */
    @Override
    public void insert(String goodsId, double saleMoney, double cost, double profit,int count) {
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        String sql = "insert into profits values(?,?,?,?,?,?)";
        try {
            runner.update(connection,sql,null,goodsId,saleMoney,cost,profit,count);
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    /**
     * 查询表中是否存在某商品
     * @param goodsId
     * @return
     */
    @Override
    public boolean isExist(String goodsId) {
        String sql = "select * from profits where goodsId = ?";
        BeanHandler<Profits>handler = new BeanHandler<>(Profits.class);
        Profits query = null;
        try {
            query = runner.query(connection, sql, handler, goodsId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (query != null) return true;
        return false;
    }

    /**
     * 表中已经有数据，则进行修改操作
     * @param goodsId
     * @param newAmount
     * @param newPrice
     */
    @Override
    public void update(String goodsId,int newAmount,double newPrice,double newCost,double newProfits) {
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        String sql = "update profits set amount = amount +?,saleMoney = saleMoney + ?,cost = cost + ?,profit = profit + ? where goodsId = ?";
        try {
            runner.update(connection,sql,newAmount,newPrice,newCost,newProfits,goodsId);
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    /**
     * 查看门店销售数据
     * @return
     */
    @Override
    public List<Profits> saleInfo() {
        String sql = "select p.id,g.NAME,p.SALEMONEY,p.COST,p.PROFIT,p.AMOUNT\n" +
                "from profits p,goods g\n" +
                "where p.GOODSID = g.ID";
        BeanListHandler<Profits> handler = new BeanListHandler<>(Profits.class);
        List<Profits> profitsList = null;
        try {
            profitsList = runner.query(connection, sql, handler);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return profitsList;
    }
}
